Endowment Spenddown

## Calculating Spend Down, NOT including the CYMs   
## 100 - (EYE/BYB * 100)
spend_down_calc1 <- endowment_data %>%
  filter(!is.na(CYBeginningYearBalanceAmt)) %>%
  mutate(spend_down = 100 - (CYEndYearBalanceAmt/CYBeginningYearBalanceAmt * 100)) %>%
  arrange(desc(spend_down)) %>%
  select(EIN, CYEndYearBalanceAmt, CYBeginningYearBalanceAmt, spend_down)

spend_down_calc1 %>%   
  filter(!is.na(spend_down) & spend_down != -Inf) %>%
  summarize(avg_spend_down = mean(spend_down),
            median_spend_down = median(spend_down),
            sd_spend_down = sd(spend_down))

ggplot(spend_down_calc1, aes(x = spend_down)) +
  geom_histogram(binwidth = 10) + 
  xlab("Spend Down") + 
  ggtitle(label = "Histogram", subtitle = "Spend Down = 100 - (EYB / BYB * 100)")

## Calculating Spend Down, NOT including the CYMs   
## EYE/BYB * 100
spend_down_calc2 <- endowment_data %>%
  filter(!is.na(CYBeginningYearBalanceAmt)) %>%
  mutate(spend_down = (CYEndYearBalanceAmt/CYBeginningYearBalanceAmt * 100)) %>%
  arrange(desc(spend_down)) %>%
  select(EIN, CYEndYearBalanceAmt, CYBeginningYearBalanceAmt, spend_down)

spend_down_calc2 %>%   
  filter(!is.na(spend_down) & spend_down != -Inf) %>%
  summarize(avg_spend_down = mean(spend_down),
            median_spend_down = median(spend_down),
            sd_spend_down = sd(spend_down))

ggplot(spend_down_calc2, aes(x = spend_down)) +
  geom_histogram(binwidth = 10) + 
  xlab("Spend Down") + 
  ggtitle(label = "Histogram", subtitle = "Spend Down = (EYB / BYB * 100)")

## Calculating Spend Down, NOT including the CYMs   
## (EYB - BYB)/BYB * 100
## Rose has notes on her choice for this calculation
spend_down <- endowment_data %>%
  filter(!is.na(CYBeginningYearBalanceAmt)) %>%
  mutate(spend_down = CYEndYearBalanceAmt - CYBeginningYearBalanceAmt,
         pct_spend_down = spend_down/CYBeginningYearBalanceAmt * 100) %>%
  arrange(desc(pct_spend_down)) %>%
  left_join(names, by = "EIN")

# Basic summary stats
spend_down %>%   
  filter(!is.na(pct_spend_down) & pct_spend_down != Inf) %>%
  summarize(avg_spend_down = mean(pct_spend_down),
            median_spend_down = median(pct_spend_down),
            sd_spend_down = sd(pct_spend_down))

spend_down %>%   
  filter(!is.na(pct_spend_down) & pct_spend_down != Inf) %>%
  group_by(EIN) %>%
  summarize(avg_spend_down = mean(pct_spend_down),
            median_spend_down = median(pct_spend_down),
            sd_spend_down = sd(pct_spend_down))

# Basic histogram summarizing it
ggplot(spend_down, aes(x = pct_spend_down)) +
  geom_histogram(binwidth = 20) + 
  xlab("% Spend Down\n(EYB - BYB) / BYB * 100") + 
  ggtitle(label = "Percentage of Spend Down", subtitle = "Red Line indicates 100%") +
  theme_classic() + 
  geom_vline(xintercept = 100, color = "maroon", linetype = "dotted")

spend_down %>%
  filter(pct_spend_down != Inf) %>%
  select(organization_name, fiscal_year, pct_spend_down) %>%
  make_table(title = "Percent Spend Down", col_names = c("Name", "Fiscal Year", "% Spend Down")) %>%
  scroll_box(height = "450px")

## Spend Down over Time
spend_down_plot <- spend_down %>%
  ggplot(aes(x = fiscal_year, y = pct_spend_down, 
             group = organization_name, color = organization_name)) +
  geom_line(alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Spend Down",
       x = "Fiscal Year",
       title = "Percentage of Endowment Spend Down",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20)) +
  geom_hline(yintercept = 100, linetype = "dotted", color = "gray")
ggplotly(spend_down_plot) %>%
  layout(showlegend = FALSE)

## Spend Down over Time
### WITHOUT ARIAZONA
spend_down_plot <- spend_down %>%
  filter(organization_name != "Ballet Arizona") %>% 
  ggplot(aes(x = fiscal_year, y = pct_spend_down, 
             group = organization_name, color = organization_name)) +
  geom_line(show.legend = FALSE, alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Spend Down",
       x = "Fiscal Year",
       title = "Percentage of Endowment Spend Down",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::breaks_pretty(n = 20)) +
  geom_hline(yintercept = 100, linetype = "dotted", color = "gray")
ggplotly(spend_down_plot) %>%
  layout(showlegend = FALSE)

##Plot with Y scale between -100 and 100 
limited_scale <- spend_down %>%
  ggplot(aes(x = fiscal_year, y = pct_spend_down, 
             group = organization_name, color = organization_name)) +
  geom_line(show.legend = FALSE, alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Spend Down",
       x = "Fiscal Year",
       title = "Percentage of Endowment Spend Down (max 100)",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20),
                     limits = c(-100, 100))
ggplotly(limited_scale) %>%
  layout(showlegend = FALSE)

## Pandemic Years
spend_down_plot <- spend_down %>%
  filter(fiscal_year %in% c("2018", "2019", "2020", "2021", "2022")) %>% 
  ggplot(aes(x = fiscal_year, y = pct_spend_down, 
             group = organization_name, color = organization_name)) +
  geom_line(show.legend = FALSE, alpha = 0.5) + 
  theme_bw() + 
  labs(y = "Percent Spend Down",
       x = "Fiscal Year",
       title = "Percentage of Endowment Spend Down",
       subtitle = "Within Pandemic Years") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20)) +
  geom_hline(yintercept = 100, linetype = "dotted", color = "gray")
ggplotly(spend_down_plot)  %>%
  layout(showlegend = FALSE)

## Table of available in-pandemic data 
spend_down %>%
  filter(fiscal_year %in% c("2019", "2020", "2021", "2022")) %>%
  select(organization_name, pct_spend_down, fiscal_year) %>%
  arrange(desc(fiscal_year)) %>%
  make_table(title = "Percent Spend Down within Pandemic Years", col_names = c("Name", "% Spend Down", "Year")) %>%
  scroll_box(height = "450px")

spend_down %>%
  filter(fiscal_year %in% c("2019", "2020", "2021", "2022")) %>%
  select(organization_name, pct_spend_down, fiscal_year) %>%
  group_by(fiscal_year) %>%
  summarize(total_in_year = n())

## Ranges of different spend-downs  
# reorder(organization_name, pull(summarize(spend_down, sd = sd(group_by(spend_down, pct_spend_down)))), na.rm = TRUE)
# Reordering by standard deviation of pct_spend down
spend_down_box <- spend_down %>%
  group_by(organization_name) %>%
  filter(pct_spend_down != Inf) %>%
  summarize(sd = sd(pct_spend_down, na.rm = TRUE)) %>%
  right_join(spend_down, by = "organization_name") %>%
  select(organization_name, EIN, pct_spend_down, sd) %>%
  mutate(organization_name = reorder(organization_name, -sd, na.rm = TRUE))

## Unlimited 
box_plot <- ggplot(spend_down_box, aes(x = organization_name, y = pct_spend_down)) + 
  geom_boxplot(aes(color = organization_name), show.legend = FALSE) + 
  geom_point(size = 1, alpha = 0.5) + 
  theme_bw() + 
  labs(title = "Range of Endowment Spend Downper Company",
       x = "Dance Company",
       y = "Percent of Endowment Spend Down") + 
  theme(axis.text.x = element_blank()) + 
  geom_hline(yintercept = 100, linetype = "dotted", color = "maroon")
ggplotly(box_plot) %>%
  layout(showlegend = FALSE)

##Limited to 100 for visibility 
box_plot_lim <- ggplot(spend_down_box, aes(x = organization_name, y = pct_spend_down)) + 
  geom_boxplot(aes(color = organization_name), show.legend = FALSE) + 
  geom_point(size = 1, alpha = 0.5) + 
  theme_bw() + 
  labs(title = "Range of Endowment Spend Down (Max of 100) per Company",
       x = "Dance Company",
       y = "Percent of Endowment Spend Down") + 
  theme(axis.text.x = element_blank()) + 
  scale_y_continuous(breaks = scales::breaks_pretty(n = 20),
                     limit = c(-100,100))
ggplotly(box_plot_lim) %>%
  layout(showlegend = FALSE)